今天想回答sql server - Select Consecutive Numbers in SQL爬文發現,自己居然一直不知道spt_values用法 (羞愧Orz..),所以寫文章分享給不小心不知道的版友。

連續整數是一個常見的SQL需求,而SQL-Server的開發者除了遞迴的方式外,其實還有一個方式能解決需求,並且效率更好。

舉例 :
在S.O想要知道某個帳號在24小時每小時發文comment總次數,來研究使用的時間利用情況,如下圖,我們能清楚了解該用戶在2-9點期間都是在休息的。

小時	總評論次數
1	497
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	27
10	140
11	311
12	11
13	386
14	242
15	318
16	193
17	42
18	261
19	423
20	86
21	364
22	132
23	223
24	95

因為我們都知道S.O使用的是SQL-Server資料庫,通常第一直覺作法是使用recursive CTE(遞迴)解決

e.g:

with cte_hours as (
    select 1 as hour
    union all
    select hour + 1
    from cte_hours
    where hour < 24
)
select h.hour ,c.count
from cte_hours h 
left join (
    select datepart(hour, creationdate) as hour,count(1) count
    from comments
    where userid = '9131476'
    group by datepart(hour, creationdate)
) c on h.hour = c.hour
order by h.hour;

online demo link : consecutive number query demo - Stack Exchange Data Explorer

但這樣這會感覺殺雞用牛刀,並且遞迴數量一大效率問題就會出現。

其實在master有一個表格spt_values我們可以拿來利用,原因是它本身有一個欄位number就是現成的連續整數,可以將SQL改成以下這樣 :

select h.hour ,c.count
from (
    select top 24 number + 1 as hour from master..spt_values 
    where type = 'P'
) h 
left join (
    select datepart(hour, creationdate) as hour,count(1) count
    from comments
    where userid = '9131476'
    group by datepart(hour, creationdate)
) c on h.hour = c.hour
order by h.hour;

online demo link : consecutive number query demo - Stack Exchange Data Explorer

以上方式算是取巧,但優點是通用性好,不需要新增任何表格直接就能使用。

另外思考:假如2048數量不夠用呢?

那何不換位思考,嘗試cross join呢,這樣就有2048*2048數量能使用了,但這時就需要用到rownumber函數了

select top 10000 row_number() over (order by t1.number) as number 
from master..spt_values T1,master..spt_values T2 
where T1.type = 'P'

最後建議

以上方式終究還是有點效率不完美,假如真的遇到大量數據需要連號 + 多列情況,麻煩建立一個實體Table + PK

幫助我

假如平常我的回答或是文章有幫助到你,可以幫我的Linkedin加個聯絡人並點擊認同技能
Wei | LinkedIn
或是Github點個追蹤或是Star,謝謝你 ^_^
Wei | Github